package SiTuQingYou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import SiTuQingYou.util.DBHelper;
import SiTuQingYou.util.Pager;
import SiTuQingYou.vo.City;
import SiTuQingYou.vo.LikeCollect;
import SiTuQingYou.vo.Scene;
import SiTuQingYou.vo.Strategy;
import SiTuQingYou.vo.User;

public class LikeCollectDao
{
	private  LikeCollectDao() {}
	private static  LikeCollectDao lcd= new  LikeCollectDao();
	public static  LikeCollectDao getlcd()
	{
		return lcd;
	}
	//改为收藏状态
	public void changeOneCollect(int userId,int strategyId)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql= "update likecollect set collect=1 where userid=? and strategyid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, strategyId);
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		
	}
	//改为点赞状态
		public void changeOneLike(int userId,int strategyId)
		{
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				conn=DBHelper.getDB().getConn();
				String sql= "update likecollect set love=1 where userid=? and strategyid=?";
				ps=conn.prepareStatement(sql);
				ps.setInt(1, userId);
				ps.setInt(2, strategyId);
				ps.executeUpdate();
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			
		}
	
	//假删除一个收藏，根据用户id和攻略id
	public void deleteOneCollect(int userId,int strategyId)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql= "update likecollect set collect=0 where userid=? and strategyid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, strategyId);
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		
	}
	//假删除一个点赞，根据用户id和攻略id
		public void deleteOneLike(int userId,int strategyId)
		{
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				conn=DBHelper.getDB().getConn();
				String sql= "update likecollect set love=0 where userid=? and strategyid=?";
				ps=conn.prepareStatement(sql);
				ps.setInt(1, userId);
				ps.setInt(2, strategyId);
				ps.executeUpdate();
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			
		}
	
	//添加一个收藏，根据用户id和攻略id
	public void addOneCollect(int userId,int strategyId )
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql= "insert into likecollect(userid,strategyid,love,collect)value(?,?,0,1)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, strategyId);
			ps.executeUpdate();
			}
		 catch (Exception e) {
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			
		}
	
	//添加一个点赞，根据用户id和攻略id
		public void addOneLike(int userId,int strategyId )
		{
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				conn=DBHelper.getDB().getConn();
				String sql= "insert into likecollect(userid,strategyid,love,collect)value(?,?,1,0)";
				ps=conn.prepareStatement(sql);
				ps.setInt(1, userId);
				ps.setInt(2, strategyId);
				ps.executeUpdate();
				}
			 catch (Exception e) {
					e.printStackTrace();
				}
				finally
				{
					DBHelper.getDB().close(rs, ps, conn);
				}
				
			}
	//检查该用户收藏的攻略是有收藏历史，存在返回true,不存在返回false	
	public boolean checkCollectHistory(int id,int userid)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
		conn=DBHelper.getDB().getConn();
		String sql="select * from likecollect where userid=? and strategyid=? ";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, userid);
		ps.setInt(2, id);
		rs=ps.executeQuery();
		return rs.next();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return false;
	}
	//检查该用户收藏的攻略是否已经存在，存在返回true,不存在返回false
	public boolean checkcollect(int id,int userid)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
		conn=DBHelper.getDB().getConn();
		String sql="select * from likecollect where userid=? and strategyid=? and collect='1'";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, userid);
		ps.setInt(2, id);
		rs=ps.executeQuery();
		return rs.next();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return false;
	}
	//检查该用户点赞的攻略是否已经存在，存在返回true,不存在返回false
	public boolean checkLike(int id,int userid)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
		conn=DBHelper.getDB().getConn();
		String sql="select * from likecollect where userid=? and strategyid=? and love='1'";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, userid);
		ps.setInt(2, id);
		rs=ps.executeQuery();
		return rs.next();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return false;
	}
	
	
	//通过用户id，查询出他所有收藏的攻略
		public ArrayList<LikeCollect> lookForAllCollectedStrategy(int userid,Pager pager)
		{
			//首先执行getCollectedStrategyNumber方法，返回数据总数，把数据总数放进pager对象里
			pager.setDataCount(this.getCollectedStrategyNumber(userid));
			
			ArrayList<LikeCollect> allStrategy = new ArrayList<LikeCollect>();
			
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			
			
			try 
			{
				conn = DBHelper.getDB().getConn();
				//String sql = "select * from strategy,scene,city where userid=? and scene.id=scenicspotid and city.id=cityid";
				String sql = "select * from strategy,likecollect,scene,city where likecollect.userid=? and scenicspotid=scene.id"
						+ " and cityid=city.id and strategyid=strategy.id and collect=1 and auditstatus=1 ORDER BY stime limit ?,?";
				ps=conn.prepareStatement(sql);
				
				ps.setInt(1, userid);
				ps.setInt(2, pager.getFirstRow());//获得limit后面第一个数是几
				ps.setInt(3, pager.getPageSize());//获得每页几条记录
				rs = ps.executeQuery();
				while(rs.next())
				{	
					LikeCollect lc = new LikeCollect();
					lc.setId(rs.getInt("likecollect.id"));
					
					User user = new User();
					user.setId(rs.getInt("likecollect.userid"));//1用户
					lc.setUser(user);
					
					
					Strategy str = new Strategy();
					str.setPhoto(rs.getString("photo"));//2攻略图片
					str.setTitle(rs.getString("title"));//3攻略标题
					str.setId(rs.getInt("strategy.id"));
					   Scene scene = new Scene();
					   scene.setScenename(rs.getString("scenename"));//4(所属)景点名称
					   City city = new City();
					   city.setCityname(rs.getString("cityname"));//5(所属)城市名称
					   city.setId(rs.getInt("city.id"));
					scene.setCity(city);//把city对象装进scene对象中
					scene.setId(rs.getInt("scene.id"));
					str.setScene(scene);//把scene对象装进strategy对象中
					lc.setStrategy(str);//把strategy对象装进likecollect对象中
					  
					allStrategy.add(lc);
					
				}
				return allStrategy;
			} 
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			return null;
		}
		
		
		//删除(假删除)选中的收藏攻略
		public void deleteAllCheckedStrategy(String cid[])
		{
			String s = "";
			for(String id:cid)
			{
				s = s+","+id;
			}
			s = s.substring(1);
			
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			
			
			try 
			{
				conn = DBHelper.getDB().getConn();
				//String sql = "delete from likecollect where id in("+s+")";
					String sql = "update likecollect set collect=0 where id in("+s+")";
				ps = conn.prepareStatement(sql);
				ps.executeUpdate();
				
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
		}
		
		//该攻略被收藏过多少次
		public int getcoullct(int strategyid)
		{
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			
			int count=0;
			try 
			{
				conn = DBHelper.getDB().getConn();
				String sql = "select count(id) as collectnum from likecollect where  strategyid=? and collect=1";
				ps = conn.prepareStatement(sql);
				ps.setInt(1,strategyid);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt("collectnum");
				}
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			return count;
			
		}
	//该攻略被点赞多少次
		public int getlike(int strategyid)
		{
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			
			int count=0;
			try 
			{
				conn = DBHelper.getDB().getConn();
				String sql = "select count(id) as likenum from likecollect where  strategyid=? and love=1";
				ps = conn.prepareStatement(sql);
				ps.setInt(1,strategyid);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt("likenum");
				}
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			return count;
			
		}
		
		//查询出此用户总共收藏了多少条攻略
		public int getCollectedStrategyNumber(int userid)
		{
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			
			int count=0;
			try 
			{
				conn = DBHelper.getDB().getConn();
				String sql = "select count(id) as collectedNum from likecollect where userid=? and collect=1";
				ps = conn.prepareStatement(sql);
				ps.setInt(1,userid);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt("collectedNum");
				}
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			return count;
			
		}
		
		
	
	
}
